<html>
<body>
	<table border="1">
		<tr>
			<td valign="top">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="index.html" />
			</td>
			<td><%@ page language="java" import="java.sql.*,java.util.*,java.io.*"%>
			<%@ page import="javax.sql.rowset.CachedRowSet,com.sun.rowset.CachedRowSetImpl;" %>
			<p>Display the classes currently taken by student X</p>
				<%
					Connection conn = null;
					try {
						// Load Oracle Driver class file
						DriverManager
								.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());

						// Make a connection to the MS SQL database "cse132b"
						conn = DriverManager
								.getConnection(
										"jdbc:sqlserver://localhost\\SQLEXPRESS;databaseName=cse132b",
										"cse132b", "cse132b");
	
						String action = request.getParameter("action");
						// Check if an insertion is requested
						CachedRowSet cachedQuery = new CachedRowSetImpl();
						// Get current quarter.
						InputStream in = application.getResourceAsStream("settings.properties");
						Properties p = new Properties();
						p.load(in);
						String quarter = p.getProperty("quarter");
						int year = Integer.parseInt(p.getProperty("year"));
						
						if (action != null && action.equals("submit")) {
							// Begin transaction
							conn.setAutoCommit(false);
							
							
							PreparedStatement pstmt = conn
									.prepareStatement("SELECT course_number, title, enroll.section_id, units, quarter, year FROM enroll, section, class "+
							          "WHERE enroll.student_id= ? AND enroll.section_id = section.section_id "
							          + "AND section.class_id = class.class_id AND class.quarter = '" + quarter + "' AND class.year = " + year);
							pstmt.setString(1, request.getParameter("student_id")); 
							
							ResultSet rs =pstmt.executeQuery();
							cachedQuery.populate(rs);
							conn.setAutoCommit(true);
						}
						
						

						// Create the statement
						Statement statement = conn.createStatement();
						
						ResultSet rsStudent = statement.executeQuery("SELECT ssn, student.student_id, first_name, middle_name, last_name FROM student, student_enrollment " +
								"WHERE student.student_id = student_enrollment.student_id AND student_enrollment.quarter = '" + quarter + "' AND student_enrollment.year = " + year);
						CachedRowSet cachedStudent = new CachedRowSetImpl();
						cachedStudent.populate(rsStudent);
				%>
				<table border="1">
					<tr>
						<form action="report1a.jsp" method="POST">
							<input type="hidden" value="submit" name="action">
							<th>
								<select name="student_id" style="width:200px">
								<%
									String student_id = request.getParameter("student_id");
									while(cachedStudent.next()){
								%>
									<option value=<%=cachedStudent.getString("student_id")%> 
										<%
											if (student_id != null && student_id.equals(cachedStudent.getString("student_id")))
												out.println("selected=selected");
										%>>
										<%= cachedStudent.getString("student_id") %> 
										<%= cachedStudent.getString("first_name") %> 
										<%= cachedStudent.getString("middle_name") %> 
										<%= cachedStudent.getString("last_name") %> 
										SSN: <%= cachedStudent.getString("ssn") %> 
									</option>
								<% 	
								}
								%>
								</select>
							</th>
							<th><input type="submit" value="submit"></th>
						</form>
					</tr>
					<tr>
						<th>Course Number</th>
						<th>Course Title</th>
						<th>Quarter</th>
						<th>Year</th>
						<th>Section ID</th>
						<th>Units Taken</th>
					</tr>
					
					<%
						// Iterate over the ResultSet
						while (cachedQuery.next()) {
							
					%>
					<tr>
                        <td><%=cachedQuery.getString("course_number") %></td>
                        <td><%=cachedQuery.getString("title") %></td>
                        <td><%=cachedQuery.getString("quarter") %></td>
                        <td><%=cachedQuery.getInt("year") %></td>
                        <td><%=cachedQuery.getInt("section_id") %></td>
                        <td><%=cachedQuery.getInt("units") %></td>
                    </tr>
                <%
						}
                %>
				<%
					} catch (SQLException e) {
						System.out.println(e.getMessage());
						//conn.rollback();
					} catch (Exception e) {
						System.out.println(e.getMessage());
						//conn.rollback();
					}
				%>
				</table>
			</td>
		</tr>
	</table>
</body>
</html>